﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using libDAL;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Net;

namespace clsEmployeeList.BUS
{
    public abstract class clsEmpListBUS : IDisposable
    {
        private string strTempConStr = @"Data Source=SOFTPC06\SERVERARCHIE;Initial Catalog=ESB_Security;Integrated Security=True";
        private cls_DAL _DAL;
        //private DataSet dsEmployeeList;
        private clsQueryList _Query;
        public clsEmpListBUS()
        {
            _DAL = new cls_DAL(strTempConStr);
            _Query = new clsQueryList();
        }

        public cls_DAL get_DAL
        {
            get
            {
                return _DAL;
            }
        }

        public bool set_DeleteEmployeeRecord(string strID)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.set_GenQueryExNonQuery(_Query.get_QueryDeleteEmployee(strID));  
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_set_DeleteEmployeeRecord");
            }

            return bolStatus;
        }

        public bool set_UntagAllOnUse(string strCurrentUser)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.set_GenQueryExNonQuery(_Query.get_queryUntagAllCurrentUseEmployeeByUser(strCurrentUser));  
            }
            catch (Exception ex)
            { 
            }
            return bolStatus;
        }

        public bool set_TagUnTagOnUse(string strEmployeeID, int intStatus, string strOnUseBy)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.set_GenQueryExNonQuery(_Query.get_queryUpdateEmployeeInfoInUse(intStatus, strOnUseBy, strEmployeeID)); 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_set_TagUnTagOnUse"); 
            }

            return bolStatus;
        }

        public bool get_CheckEmployeeIDExists(string strEmployeeID)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.get_CheckAnythingExists(_Query.get_queryCheckIDExists(strEmployeeID));
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_get_CheckEmployeeIDExists"); 
            }

            return bolStatus;
        }

        public  bool get_CheckEmployeeIDOnUse(string strEmployeeID, string strCurrentUser)
        {
            
                bool bolStatus = false;
            try
            {
                bolStatus = _DAL.get_CheckAnythingExists(_Query.get_QueryEmployeeIDOnUse(strEmployeeID, strCurrentUser));

                if (bolStatus == true)
                {
                    bolStatus = true;
                }
                else
                {
                    bolStatus = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_get_CheckEmployeeIDExists"); 
            }

            return bolStatus;
        }

        public bool set_UpdateEmployeeProfile(string strEmployeeID, string strFirstName,
                                                    string strLastName, string strMI, string strDesignation,
                                                    string strUserLevel, string strPassword, string strBranch,
                                                    string strLastModifiedBy, string strUserName, int intStatus)
        {

            bool bolStatus = false;
            try
            {
               bolStatus = _DAL.set_GenQueryExNonQuery(_Query.get_queryUpdateEmployeeProfile( strEmployeeID,  strFirstName,
                                                      strLastName, strMI,  strDesignation,
                                                      strUserLevel,  strPassword,  strBranch,
                                                      strLastModifiedBy,  strUserName, intStatus)); 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_set_UpdateEmployeeProfile"); 
            }

            return bolStatus;
        }


        

        public bool set_InsertEmployeeProfile(string strEmployeeID, string strFirstName,
                                              string strLastName, string strMI, string strDesignation,
                                              string strUserLevel, string strPassword, string strBranch,
                                              string strLastModifiedBy, string strUserName, int intStatus)
        { 
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.set_GenQueryExNonQuery (_Query.get_queryInsertEmployeeProfile( strEmployeeID,  strFirstName,
                                                                                         strLastName,  strMI,  strDesignation,
                                                                                         strUserLevel,  strPassword,  strBranch,
                                                                                         strLastModifiedBy,  strUserName, intStatus)); 
            }
            catch (Exception ex)
            { 
                 MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_set_InsertEmployeeProfile"); 
            }
            return bolStatus;
        }

        public bool set_EmployeeActualAppReport(ref DataSet dsAppReportList, string strTableName, string strEmployeyID)
        {
            bool bolStatus = false;
            try
            {
                bolStatus = _DAL.set_DataWCardinality_ds(_Query.get_queryAppReportAccess(strEmployeyID), ref dsAppReportList,strTableName); ; 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " libEmployeeList_clsEmpListBUS_set_EmployeeActualAppReport"); 
            }
            return bolStatus;
        }

        public bool get_EmployeeList(ref DataSet dsEmployeeList, string strTableName)
        {
            bool bolStatus = false;
            try
            {
                dsEmployeeList = new DataSet();
                bolStatus = _DAL.set_DataWCardinality_ds(_Query.get_queryEmployeeList, ref dsEmployeeList, strTableName); 
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " libEmployeeList_clsEmpListBUS_set_EmployeeList"); 
            }
            return bolStatus;
        }

        public bool get_CheckPassword(string strPassword)
        { 
            bool bolStatus = false;
            try
            {

            }
            catch (Exception ex)
            { 
            }
            return bolStatus;
        }

        public void set_CleanMemory()
        {
            _DAL = null;
            GC.Collect();
        }

        public void Dispose()
        {
            _DAL.Close_Connection();
            _DAL = null;
            GC.Collect();
        }

        public bool set_EmployeeAccessList(string strEmployeeID, ref DataTable dtList)
        {
            bool bolStatus = false;
            try
            {
                dtList = _DAL.get_TableDetail(_Query.get_queryAppReportAccessJoined(strEmployeeID));
                bolStatus = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "libEmployeeList_clsEmpListBUS_set_EmployeeAccessList"); 
            }

            return bolStatus;
        }

        
    }

    public sealed class clsQueryList
    {
        public string get_QueryDeleteEmployee(string strID)
        {
            return "delete from EmployeeAccess where EmployeeID = '" + strID + "'; " +
                   "delete from EmployeesInfo where EmployeeID = '" + strID + "'";
        }

        public string get_queryEmployeeList
        {
            get 
            {
                return "SELECT EmployeeID, eiFirstname as eiFirstname, eiLastName as eiLastName, " +
                       "eiMiddleName, eiDesignation, " +
                       "eiPassword, eiBranch, eiUserName, DateTimeInserted, DateTimeUpdated, LastModifiedBy, " +
                       "HostNameIPAddress,eiStatus " +
                       "FROM EmployeesInfo";
            }
        }

        public string get_queryUserAccessLevel(string strUserID)
        {
            return "SELECT AccessLevels.AccessLevelID, AccessLevels.AccessLevelName, " +
                   "AccessLevels.AccessLevelDescription, UserAccessLevels.EmployeeID " +
                   "FROM AccessLevels LEFT OUTER JOIN " +
                   "UserAccessLevels ON AccessLevels.AccessLevelID = UserAccessLevels.AccessLevelID " +
                   "WHERE LTRIM(RTRIM(UserAccessLevels.EmployeeID)) = '" + strUserID.Trim() + "' " +
                   "OR UserAccessLevels.EmployeeID is null or UserAccessLevels.EmployeeID = ''";
        }

        public string get_QueryModuleApprovals(string strUserID, string strModuleID)
        {
            return "SELECT UserModuleApprovals.EmployeeID, ModuleApprovals.ApprovalName, ModuleApprovals.ApprovalsID, ModuleApprovals.ModuleID " +
                   "FROM UserModuleApprovals RIGHT OUTER JOIN " +
                   "ModuleApprovals ON UserModuleApprovals.ApprovalsID = ModuleApprovals.ApprovalsID " +
                   "WHERE (LTRIM(RTRIM(UserModuleApprovals.EmployeeID)) = '" + strUserID + "' AND " +
                   "LTRIM(RTRIM(ModuleApprovals.ModuleID)) = '" + strModuleID + "') OR " + 
                   "(UserModuleApprovals.EmployeeID IS NULL AND LTRIM(RTRIM(ModuleApprovals.ModuleID)) = '" + strModuleID + "')";
        }

        public string get_queryUserDBAccess(string strUserID)
        {
            return "SELECT UserAccessLocations.EmployeeID, AccessLocations.AccessLocationsID, " +
                   "AccessLocations.alName, AccessLocations.alDescription, AccessLocations.alConnectionString " +
                   "FROM AccessLocations LEFT OUTER JOIN " +
                   "UserAccessLocations ON AccessLocations.AccessLocationsID = UserAccessLocations.AccessLocationsID " +
                   "WHERE LTRIM(RTRIM(UserAccessLocations.EmployeeID)) = '" + strUserID + "' " +
                   "OR UserAccessLocations.EmployeeID is null or UserAccessLocations.EmployeeID = ''";
        }

        public string get_queryCheckIDExists(string strID)
        {
            return "SELECT EmployeeID from EmployeesInfo where EmployeeID = '" + strID.Trim() + "' ";
        }

       public string get_queryUpdateEmployeeProfile(string strEmployeeID, string strFirstName,
                                                     string strLastName, string strMI, string strDesignation,
                                                     string strUserLevel, string strPassword, string strBranch,
                                                     string strLastModifiedBy, string strUserName, int intStatus)
        { 
           return "UPDATE [EmployeesInfo] " +
                  "SET [eiFirstname] = '" + strFirstName + "'" +
                  ",[eiLastName] = '" + strLastName + "' " +
                  ",[eiMiddleName] = '" + strMI + "' " +
                  ",[eiDesignation] = '" + strDesignation + "'"+
                  ",[eiPassword] = '" + strPassword + "' "+
                  ",[eiBranch] = '" + strBranch + "' "+
                  ",[DateTimeUpdated] = GETDATE () " +
                  ",[LastModifiedBy] =  '" + strLastModifiedBy + "' "+
                  ",[HostNameIPAddress] = '" + clsGenFunction.getHostName() + "'" +
                  ",[eiUserName] = '" + strUserName + "' "+
                  ", eiStatus = " + intStatus + " " +
                  " WHERE ltrim(rtrim(EmployeeID)) = '" + strEmployeeID + "'";
        }

        public string get_queryInsertEmployeeProfile(string strEmployeeID, string strFirstName,
                                                     string strLastName, string strMI, string strDesignation,
                                                     string strUserLevel, string strPassword, string strBranch,
                                                     string strLastModifiedBy, string strUserName, int intStatus)
        {
            return " INSERT INTO EmployeesInfo " +
                   "([EmployeeID] ,[eiFirstname] " +
                   ",[eiLastName], [eiMiddleName] " +
                   ",[eiDesignation] " +
                   ",[eiPassword] ,[eiBranch] " +
                   ",[DateTimeInserted], [DateTimeUpdated] " +
                  ",[LastModifiedBy],[HostNameIPAddress] " +
                  ",[eiUserName],eiStatus ) " +
                  "VALUES ('" + strEmployeeID + "', '" + strFirstName + "', " +
                           "'" + strLastName + "', '" + strMI + "', " +
                           "'" + strDesignation + "', " +
                           "'" + strPassword + "', '" + strBranch + "', " +
                           "getdate(), getdate(), " +
                           "'" + strLastModifiedBy + "', " +
                           "'" + clsGenFunction.getHostName() + "', " +
                           "'" + strUserName + "', " + intStatus + ")";
        }

        public string get_queryUpdateEmployeeInfoInUse(int intStatus,string strOnUseBy, string strEmployeeID)
        {
            return "update EmployeesInfo set eiOnUse = " + intStatus + ", eiOnUseBy = '" + strOnUseBy + "' " +
                   "where EmployeeID = '" + strEmployeeID + "'";
        }

        public string get_queryUntagAllCurrentUseEmployeeByUser(string strOnUseBy)
        {
            return "update EmployeesInfo set eiOnUse = 0, eiOnUseBy = '' " +
                   "where ltrim(rtrim(eiOnUseBy)) = '" + strOnUseBy.Trim() + "'";
        }

        public string get_QueryEmployeeIDOnUse(string strEmployeeID, string strCurrentUser)
        {
            return "SELECT eiOnUse " +
                    "FROM EmployeesInfo " +
                    "where (EmployeeID = '" + strEmployeeID + "' and eiOnUse = 1 and eiOnUseBy = '" + strCurrentUser + "') or (eiOnUseBy is null or eiOnUseBy = '') and eiOnUse <> '0'"; 
        }

        public string get_queryAppReportAccessJoined(string strEmployeeID)
        {
            return "SELECT 	ModuleDetail.mdModulename, ModuleDetail.mdModuleDescription, " +
	               "ISNULL(EmployeeAccess.eaAccess,'0') as eaAccess, " +
	               "isnull(EmployeesInfo.EmployeeID,'') as EmployeeID_ar, " + 
	               "isnull(EmployeeAccess.EmployeeAccessID,'') as EmployeeAccessID, " +
	               "ISNULL(ModuleDetail.ModuleID,'') as ModuleID, " +
	               "ISNULL(EmployeeAccess.LastModifiedBy,'') as LastMOdifiedBy_ar, " +  
	               "EmployeeAccess.DateTimeInserted,  " +
	               "EmployeeAccess.DateTimeUpdated as DateTimeUpdated_ar, " +  
                   "isnull(EmployeeAccess.HostNameIPAddress,'') as HostNameIPAddress_ar " +
	               "FROM EmployeesInfo RIGHT OUTER JOIN  " +
                   "EmployeeAccess ON EmployeesInfo.EmployeeID = EmployeeAccess.EmployeeID RIGHT OUTER JOIN " +  
                   "ModuleDetail ON EmployeeAccess.ModuleID = ModuleDetail.ModuleID  " +
                   "where EmployeesInfo.EmployeeID = '" + strEmployeeID + "' or EmployeesInfo.EmployeeID is null or EmployeesInfo.EmployeeID = ''";
        }

        public string get_queryAppReportAccess(string strEmployeeID)
        {
            return "SELECT EmployeeAccessID, ModuleID, EmployeeID,DateTimeInserted, DateTimeUpdated, LastModifiedBy, HostNameIPAddress, " +
                   "eaAccess " +
                   "FROM EmployeeAccess " +
                   "WHERE LTRIM(RTRIM(EmployeeID)) = '" + strEmployeeID.Trim() + "'";
        }

    }
}
